package com.nononsenseapps.feeder.db.room

import android.database.Cursor
import androidx.lifecycle.LiveData
import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import androidx.room.Update
import cn.ekuma.odbo.mobile.room.BaseDao
import com.gitee.wsl.ext.base.isFalse
import com.nononsenseapps.feeder.db.FEED.COL_TITLE
import com.nononsenseapps.feeder.db.FEED.COL_ID
import com.nononsenseapps.feeder.db.entity.Feed
import com.nononsenseapps.feeder.model.FeedUnreadCount
import kotlinx.coroutines.flow.Flow
import org.joda.time.DateTime
import java.net.URL

@Dao
interface FeedDao: BaseDao<Feed> {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insertFeed(feed: Feed): Long

    @Update
    fun updateFeed(feed: Feed)

    @Delete
    fun deleteFeed(feed: Feed)

    @Query("SELECT count(*) FROM feeds WHERE url IS :feedUrl")
    fun isExitsFeed(feedUrl: String): Boolean

    @Query("SELECT count(*) FROM feeds WHERE id IS :feedId")
    fun isExitsFeed(feedId: Long): Boolean

    @Query("SELECT * FROM feeds WHERE id IS :feedId")
    fun loadLiveFeed(feedId: Long): Feed

    @Query("SELECT * FROM feeds WHERE id > 0")
    fun loadSubscribeFeeds(): List<Feed>

    @Query("SELECT DISTINCT tag FROM feeds ORDER BY tag COLLATE NOCASE")
    fun loadTags(): List<String>

    @Query("SELECT * FROM feeds WHERE id IS :feedId")
    fun loadFeed(feedId: Long): Feed?

    @Query("""
       SELECT * FROM feeds
       WHERE id is :feedId
       AND last_sync < :staleTime
       AND feed_set_sync_able IS :syncAble
    """)
    fun loadFeedIfStale(feedId: Long, staleTime: Long,syncAble:Boolean=true): Feed?

    @Query("SELECT * FROM feeds WHERE last_sync < :staleTime  AND feed_set_sync_able IS :syncAble")
    fun loadFeedsIfStale(staleTime: Long,syncAble:Boolean=true): List<Feed>

    @Query("SELECT * FROM feeds WHERE tag IS :tag AND last_sync < :staleTime AND feed_set_sync_able IS :syncAble")
    fun loadFeedsIfStale(tag: String, staleTime: Long,syncAble:Boolean=true): List<Feed>

    @Query("SELECT * FROM feeds WHERE tag IS :tag")
    fun loadFeeds(tag: String): List<Feed>

    @Query("SELECT * FROM feeds WHERE tag IS :tag AND feed_set_sync_able IS :syncAble")
    fun loadFeedsForSync(tag: String,syncAble:Boolean=true): List<Feed>

    @Query("SELECT notify FROM feeds WHERE tag IS :tag")
    fun loadLiveFeedsNotify(tag: String): List<Boolean>

    @Query("SELECT notify FROM feeds")
    fun loadLiveFeedsNotify(): List<Boolean>

    @Query("SELECT * FROM feeds")
    fun loadFeeds(): List<Feed>

    @Query("SELECT * FROM feeds WHERE feed_set_sync_able IS :syncAble")
    fun loadFeedsForSync(syncAble:Boolean=true): List<Feed>

    @Query("SELECT * FROM feeds WHERE url IS :url")
    fun loadFeedWithUrl(url: URL): Feed?

    @Query("SELECT id FROM feeds")
    fun loadFeedIds(): List<Long>

    @Query("SELECT id FROM feeds WHERE notify IS 1")
    fun loadFeedIdsToNotify(): List<Long>

    @Query("""
        SELECT id, title, url, tag, custom_title, notify, image_url, unread_count,attention_level,feed_set_sync_able,feed_last_sync_success,rule_able
        FROM feeds
        LEFT JOIN (SELECT COUNT(1) AS unread_count, feed_id
          FROM feed_items
          WHERE unread IS 1
          GROUP BY feed_id
        )
        ON feeds.id = feed_id
    """)
    fun loadLiveFeedsWithUnreadCounts(): Flow<List<FeedUnreadCount>>


    @Query("UPDATE feeds SET notify = :notify WHERE id = :id")
    fun setNotify(id: Long, notify: Boolean)

    @Query("UPDATE feeds SET notify = :notify WHERE tag = :tag")
    fun setNotify(tag: String, notify: Boolean)

    @Query("UPDATE feeds SET notify = :notify")
    fun setAllNotify(notify: Boolean)

    @Query("SELECT last_sync FROM feeds ORDER BY last_sync DESC LIMIT 1")
    fun getLastSyncTime(): LiveData<DateTime?>

    @Query("UPDATE feeds SET attention_level = :level WHERE id = :id")
    fun changeFeedAttentionLevel(id: Long, level: Int)

    @Query("DELETE FROM feeds WHERE tag = :tag")
    fun deleteFeedsInTag(tag: String)

    @Query("SELECT feed_set_open_type FROM feeds WHERE id = :feedId")
    fun findFeedViewListType(feedId: Long): Int

    @Query("UPDATE feeds SET  feed_set_open_type=:openType WHERE id = :feedId")
    fun setFeedViewListType(feedId: Long,openType:Int)

    @Query("UPDATE feeds SET feed_open_type_tree_name=:treeName WHERE id = :feedId")
    fun setFeedViewListTypeTreeName(feedId: Long,treeName:String)

    @Query("SELECT feed_open_type_tree_name FROM feeds WHERE id = :feedId")
    fun findFeedViewListTypeTreeName(feedId: Long): String

    @Query("UPDATE feeds SET  feed_set_sync_able=:readable WHERE id = :feedId")
    fun toggleFeedReadable(feedId: Long, readable: Boolean)

    @Query("UPDATE feeds SET  feed_set_sync_able=:readable WHERE tag = :tag")
    fun toggleFeedWithTagReadable(tag: String, readable: Boolean)

    @Query("UPDATE feeds SET  feed_set_sync_able=:readable WHERE id > 0")
    fun toggleAllFeedReadable(readable: Boolean)

    @Query("SELECT title FROM feeds WHERE id = :feedId")
    fun getFeedName(feedId: Long): String

    @Query("UPDATE feeds SET rule_able = :ruleAble WHERE id = :id")
    fun markFeedRuleAble(id: Long, ruleAble: Boolean)

    @Query(
        """
        SELECT $COL_ID as id, $COL_TITLE as title
        FROM feeds
        ORDER BY $COL_TITLE
    """
    )
    fun loadFeedsForContentProvider(): Cursor
}

/**
 * Inserts or updates feed depending on if ID is valid. Returns ID.
 */
fun FeedDao.upsert(feed: Feed): Long = when (feed.id > ID_UNSET) {
    true -> {
        update(feed)
        feed.id
    }
    false -> {
        val id=insert(feed)
        feed.id=id
        id
    }
}

fun FeedDao.mustExist(feed: Feed)=isExitsFeed(feedId = feed.id).isFalse {
    insert(feed)
}